package com.ld.shieldsb.db.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.DynaBean;

import com.ld.shieldsb.annotation.util.AnnotationUtil;
import com.ld.shieldsb.common.composition.util.ConvertUtil;
import com.ld.shieldsb.common.core.collections.ListUtils;
import com.ld.shieldsb.common.core.model.Result;
import com.ld.shieldsb.common.core.reflect.ClassUtil;
import com.ld.shieldsb.common.core.util.StringUtils;
import com.ld.shieldsb.db.dao.dynamic.DynamicBaseDao;
import com.ld.shieldsb.db.model.DBSetInfo;
import com.ld.shieldsb.db.model.DBTableColumnModel;
import com.ld.shieldsb.db.model.DBTableModel;
import com.ld.shieldsb.db.service.DBTabelService;

import lombok.extern.slf4j.Slf4j;

@Slf4j
/**
 * table服务mysql实现类
 *
 * @ClassName MysqlTabelService
 * @author 吕凯
 * @date 2016年8月25日 上午9:23:00
 *
 */
public class MysqlTabelService extends DBTabelService {
    private static String ALL_TABLE_SQL = "SELECT TABLE_NAME as tableName,TABLE_ROWS as dataSize,TABLE_COMMENT as "
            + "tableComments FROM information_schema.TABLES WHERE TABLE_SCHEMA = '%s'";

    private static String ALL_Field_SQL = "SELECT table_name as tableName,column_name as columnName,column_default as defaultValue,"
            + "COLUMN_TYPE as columnType,DATA_TYPE as dataType,character_maximum_length as datasize,(CASE is_NULLABLE WHEN 'YES' THEN true ELSE false END) as nullable,collation_name as collation,"
            + "COLUMN_COMMENT as remarks from information_schema.columns where table_name = '%s' and table_schema = '%s'";

    private static String TABLE_EXIST_SQL = "SELECT * FROM information_schema.tables WHERE table_name = '%s' AND table_schema = '%s'";

    public MysqlTabelService(DBSetInfo dbSetInfo) {
        super(dbSetInfo);
    }

    public List<DBTableModel> getTables1(DBSetInfo dbSetInfo, String tableNameKey) {
        List<DBTableModel> tableList = new ArrayList<>();
        String strsql = " SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA='" + dbSetInfo.getDbName() + "' ";
        if (StringUtils.isNotBlank(tableNameKey)) {
            strsql += " AND TABLE_NAME like ? ";
        }
        strsql += " ORDER BY table_name";
        ResultSet rsmd = null;
        try (Connection conn = DriverManager.getConnection(dbSetInfo.getDbUrl(), dbSetInfo.getDbUserName(), dbSetInfo.getDbPassword());
                PreparedStatement pstmt = conn.prepareStatement(strsql);) {
            if (StringUtils.isNotBlank(tableNameKey)) {
                pstmt.setString(1, "%" + tableNameKey.toUpperCase() + "%");
            }
            pstmt.executeQuery();
            rsmd = pstmt.getResultSet();

            while (rsmd.next()) {
                String tableName = rsmd.getString("TABLE_NAME");
                Long dataSize = rsmd.getLong("TABLE_ROWS");
                String comments = rsmd.getString("TABLE_COMMENT");
                DBTableModel model = new DBTableModel();
                model.setTableName(tableName);
                model.setDataSize(dataSize);
                model.setTableComments(comments);
                tableList.add(model);
            }
        } catch (Exception e) {
            log.error("查询用户" + dbSetInfo.getDbUserName() + "下的表出错！", e);
        } finally {
            if (rsmd != null) {
                try {
                    rsmd.close();
                } catch (SQLException e) {
                    log.error("关闭出错！", e);
                }
            }

        }
        return tableList;
    }

    @Override
    public List<DBTableModel> getTables(String tableNameKey) {
        List<DBTableModel> tableList = new ArrayList<>();
        String strsql = String.format(ALL_TABLE_SQL, dbSetInfo.getDbName().toUpperCase());
        List<String> paramList = new ArrayList<>();
        if (tableNameKey != null && tableNameKey.length() > 0) {
            strsql += " AND TABLE_NAME like ? ";
            paramList.add("%" + tableNameKey.toUpperCase() + "%");
        }
        strsql += " ORDER BY table_name";
        DynamicBaseDao baseDao = getDAO();

        List<Map<String, Object>> listMaps = baseDao.getSqlMapList(strsql, paramList.toArray(new Object[] {}));
        listMaps.stream().forEach(map -> {
            tableList.add(AnnotationUtil.map2model(map, DBTableModel.class));
        });
        return tableList;
    }

    /**
     * 根据表名返回列信息
     *
     * @Title getTableColumn
     * @author 吕凯
     * @date 2016年8月25日 上午10:12:06
     * @param tableName
     * @return List<DBTableColumnModel>
     */
    @Override
    public List<DBTableColumnModel> getTableColumn(String tableName) {
        List<DBTableColumnModel> tableList = new ArrayList<>();

        DynamicBaseDao baseDao = getDAO();
        String strsql = String.format(ALL_Field_SQL, tableName, dbSetInfo.getDbName().toUpperCase());
        List<Map<String, Object>> listMaps = baseDao.getSqlMapList(strsql);
        listMaps.stream().forEach(map -> {

            try {
                DBTableColumnModel obj = ConvertUtil.map2obj(map, DBTableColumnModel.class);
                String columnType = obj.getColumnType(); // bigint(20) double(10,2) text
                Integer dataSize = obj.getDatasize();
                if (dataSize == null && columnType.contains("(")) {
                    if (!columnType.contains(",")) {
                        String sizeStr = StringUtils.substringBetween("(", ")");
                        obj.setDatasize(ClassUtil.obj2int(sizeStr));
                    } else {
                        String sizeStr = StringUtils.substringBetween("(", ",");
                        String digitsStr = StringUtils.substringBetween(",", ")");
                        obj.setDatasize(ClassUtil.obj2int(sizeStr));
                        obj.setDatasize(ClassUtil.obj2int(digitsStr));
                    }

                }
                tableList.add(obj);
            } catch (Exception e) {
                log.error("转换类型出错！", e);
            }
        });

//                String columnName = colRet.getString("COLUMN_NAME");
//                String remarks = colRet.getString("REMARKS");
//                String columnType = colRet.getString("TYPE_NAME");
//                int datasize = colRet.getInt("COLUMN_SIZE");
//                int digits = colRet.getInt("DECIMAL_DIGITS");
//                int nullable = colRet.getInt("NULLABLE"); // 返回1就表示可以是Null,而0就表示Not Null。
//                DBTableColumnModel tableColumn = new DBTableColumnModel();
//                tableColumn.setTableName(tableName);
//                tableColumn.setColumnName(columnName);
//                tableColumn.setColumnType(columnType);
//                tableColumn.setDatasize(datasize);
//                tableColumn.setDigits(digits);
//                tableColumn.setRemarks(remarks);
//                tableColumn.setNullable(nullable == 1 ? true : false);
//                tableList.add(tableColumn);
//            }

        return tableList;
    }

    /**
     * 获取增加列的语句
     *
     * @Title getAddColumnSql
     * @author 吕凯
     * @date 2018年9月12日 下午5:30:16
     * @param col
     * @param beforeCol
     * @return
     * @see com.ld.util.dbcompare.DBTabelService#getAddColumnSql(com.ld.util.ExportTableColumnModel.model.DBTableColumnModel,
     *      com.ld.util.ExportTableColumnModel.model.DBTableColumnModel)
     */
    @Override
    public String getAddColumnSql(DBTableColumnModel col, DBTableColumnModel beforeCol) {
        String sql = "ALTER TABLE `" + col.getTableName() + "` ADD COLUMN `" + col.getColumnName() + "` " + col.getColumnType() + " "
                + (col.getNullable() ? "NULL" : "NOT NULL");
        sql = appendSqlStr(col, beforeCol, sql);
        return sql;
    }

    /**
     * 追加公共部分代码
     *
     * @Title appendSqlStr
     * @author 吕凯
     * @date 2018年9月12日 下午5:28:50
     * @param col
     * @param beforeCol
     * @param sql
     * @return String
     */
    private String appendSqlStr(DBTableColumnModel col, DBTableColumnModel beforeCol, String sql) {
        if (col.getCollation() != null) {
            sql += " COLLATE " + col.getCollation() + " ";
        }
        if (col.getDefaultValue() != null) {
            sql += " DEFAULT " + col.getDefaultValue() + " ";
        }
        if (StringUtils.isNotEmpty(col.getRemarks())) {
            sql += " COMMENT `" + col.getRemarks() + "` ";
        }
        if (beforeCol != null) {
            sql += " AFTER `" + beforeCol.getColumnName() + "` ";
        }
        return sql;
    }

    /**
     * 获取修改列的语句
     *
     * @Title getModifyColumnSql
     * @author 吕凯
     * @date 2018年9月12日 下午5:29:38
     * @param col
     * @param beforeCol
     * @return
     * @see com.ld.util.dbcompare.DBTabelService#getModifyColumnSql(com.ld.util.ExportTableColumnModel.model.DBTableColumnModel,
     *      com.ld.util.ExportTableColumnModel.model.DBTableColumnModel)
     */
    @Override
    public String getModifyColumnSql(DBTableColumnModel col, DBTableColumnModel beforeCol) {
//        String modifySql = "ALTER TABLE `" + col.getTableName() + "` CHANGE COLUMN `" + col.getColumnName() + "` `" + col.getColumnName()
//                + "` " + col.getColumnType() + " " + (col.getNullable() ? "NULL" : "NOT NULL");
        String modifySql = "ALTER TABLE `" + col.getTableName() + "` MODIFY COLUMN `" + col.getColumnName() + "` " + col.getColumnType()
                + " " + (col.getNullable() ? "NULL" : "NOT NULL");
        modifySql = appendSqlStr(col, beforeCol, modifySql);
        return modifySql;
    }

    // 表存在时获取建表语句
    @Override
    public String getCreateTableSql(String tableName) {
        DynamicBaseDao baseDao = getDAO();

        String sql = "SHOW CREATE table " + tableName;
        DynaBean bean = baseDao.getOne(sql);
        return bean.get("create table").toString();
    }

    @Override
    public String getCreateTableSql(DBTableModel tableModel) {
        String createTableStr = null;
        String tableComments = tableModel.getTableComments();
        if (ListUtils.isNotEmpty(tableModel.getColumns())) {
            List<String> primaryKeyList = new ArrayList<>();
            createTableStr = "CREATE TABLE `" + tableModel.getTableName() + "` (\n";

            for (DBTableColumnModel column : tableModel.getColumns()) {
                String columnName = column.getColumnName();
                String dataType = column.getDataType();

                createTableStr += "`" + columnName + "` " + dataType;
                Boolean nullable = column.getNullable();
                if (nullable != null && !nullable) {
                    createTableStr += " NOT NULL";
                }
                String defaultVal = (String) column.getDefaultValue();
                if (StringUtils.isNotBlank(defaultVal)) {
                    if (!"CURRENT_TIMESTAMP".equalsIgnoreCase(defaultVal) && !"now()".equalsIgnoreCase(defaultVal)
                            && !defaultVal.endsWith("()")) { // 非函数型
                        createTableStr += " DEFAULT '" + defaultVal + "' ";
                    } else {
                        createTableStr += " DEFAULT " + defaultVal + " ";
                    }
                }
                String remarks = column.getRemarks();
                if (StringUtils.isNotBlank(remarks)) {
                    createTableStr += " COMMENT '" + remarks + "'";
                }
                createTableStr += ",\n";
                Boolean ispk = column.getIsPrimaryKey();
                if (ispk != null && ispk) {
                    primaryKeyList.add(columnName);
                }
            }
            if (ListUtils.isNotEmpty(primaryKeyList)) {
                createTableStr += "PRIMARY KEY (`" + StringUtils.join(primaryKeyList.toArray(new String[0]), "`,`") + "`),\n";
            }
            if (createTableStr.endsWith(",\n")) { // 去除多余的,号
                createTableStr = StringUtils.substring(createTableStr, 0, -2) + "\n";
            }
            createTableStr += ")";
            if (StringUtils.isNotBlank(tableComments)) {
                createTableStr += " COMMENT '" + tableComments + "'";
            }
        }
        return createTableStr;
    }

    @Override
    public Result checkTableExist(String tableName) {
        Result result = new Result();

        DynamicBaseDao baseDao = getDAO();
        String strsql = String.format(TABLE_EXIST_SQL, tableName, dbSetInfo.getDbName().toUpperCase());
        List<Map<String, Object>> listMaps = baseDao.getSqlMapList(strsql);
        if (ListUtils.isNotEmpty(listMaps)) {
            result.setSuccess(true);
        } else {
            result.setMessage("表不存在");
        }

        return result;
    }

    @Override
    public Result createTable(String createTableStr) {
        DynamicBaseDao baseDao = getDAO();
        Result result = execSql(baseDao, createTableStr);
        return result;
    }

}
